﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Potato.Model;
using OMG.Fill.Potato.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace OMG.Fill.Potato.Services
{
    /// <summary>
    /// 马铃薯全年生产情况统计表通用操作类
    /// </summary>
    public class PotYearProductServices : PotYearProducts
    {
        public Pager<List<PotYearProductEntity>> GetPageList(PotYearProductQuery query)
        {
            Pager<List<PotYearProductEntity>> obj = new Pager<List<PotYearProductEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_PotYearProduct where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<PotYearProductEntity>(strSql, paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取马铃薯全年生产情况统计表分页列表失败", ex);
                return obj;
            }
        }
        public ResResult<MyReportModel> GetModel(string AreaCode, string FillInYear)
        {
            ReportServices reportService = new ReportServices();
            ResResult<MyReportModel> obj = new ResResult<MyReportModel>();
            MyReportModel model = new MyReportModel();
            List<PotYearProductEntity> list = new List<PotYearProductEntity>();
            int oldFillInYear = Convert.ToInt32(FillInYear) - 1;
            string strSql = "select * from T_PotYearProduct  where ReportId = @ReportId";
            if (reportService.CheckRepCreate("PotYearProduct", AreaCode, FillInYear))
            {
                obj.status = DefaultKey.ErrorResult;
                obj.model = model;
                obj.message = FillInYear + "年的全年生产情况表没有生成！";
            }
            else
            {
                ReportEntity reportModel = reportService.GetReportView("PotYearProduct", AreaCode, FillInYear);
                model.ReportStatus = reportModel.Status;
                model.ReportId = reportModel.Id;
                model.Opinion = reportModel.Opinion;
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    if (reportService.CheckRepCount(model.ReportId, "T_PotYearProduct"))
                    {
                        PotYearProductEntity entity = new PotYearProductEntity();
                        entity.ReportId = model.ReportId;
                        list.Add(entity);
                    }
                    else
                    {
                        list = dbCon.Query<PotYearProductEntity>(strSql, new { ReportId = model.ReportId }).ToList();
                    }
                    model.list = list;

                }
                obj.model = model;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "数据获取成功！";
            }
            return obj;
        }

        public List<PotYearProductEntity> ExcelToModel(string pathName, string fileSuffix, string ReportId)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<PotYearProductEntity> list = new List<PotYearProductEntity>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                int startRow = 4;
                IRow firstRow = sheet.GetRow(startRow);//从第5行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取所有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    PotYearProductEntity model = new PotYearProductEntity();
                    if (row.GetCell(0) != null&&!String .IsNullOrEmpty(row.GetCell(0).ToString()))
                    {
                        model.Pvariety = row.GetCell(0).ToString();
                    }
                    else
                    {
                        break;
                    }
                        
                    if (row.GetCell(1) != null)
                    {
                        if (row.GetCell(1).CellType == CellType.Formula)
                        {
                            model.YArea = Decimal.Round(Convert.ToDecimal(row.GetCell(1).NumericCellValue), 4);//全年合计面积(万亩）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(1).ToString()))
                            {
                                model.YArea = Convert.ToDecimal(row.GetCell(1).ToString());
                            }
                            else
                            {
                                model.YArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.YArea = 0;
                    }

                    if (row.GetCell(1) != null)
                    {
                        if (row.GetCell(1).CellType == CellType.Formula)
                        {
                            model.YArea = Decimal.Round(Convert.ToDecimal(row.GetCell(1).NumericCellValue), 4);//全年合计面积(万亩）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(1).ToString()))
                            {
                                model.YArea = Convert.ToDecimal(row.GetCell(1).ToString());
                            }
                            else
                            {
                                model.YArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.YArea = 0;
                    }
                    
                    if (row.GetCell(2) != null)
                    {
                        if (row.GetCell(2).CellType == CellType.Formula)
                        {
                            model.YYield = Decimal.Round(Convert.ToDecimal(row.GetCell(2).NumericCellValue),4);//全年合计总产(万吨）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(2).ToString()))
                            {
                                model.YYield = Convert.ToDecimal(row.GetCell(2).ToString());
                            }
                            else
                            {
                                model.YYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.YYield = 0;
                    }

                    if (row.GetCell(3) != null)
                    {
                        if (row.GetCell(3).CellType == CellType.Formula)
                        {
                            model.YOutput = Decimal.Round(Convert.ToDecimal(row.GetCell(3).NumericCellValue), 4);//全年合计总产值(万元）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(3).ToString()))
                            {
                                model.YOutput = Convert.ToDecimal(row.GetCell(3).ToString());
                            }
                            else
                            {
                                model.YOutput = 0;
                            }
                        }
                    }
                    else
                    {
                        model.YOutput = 0;
                    }


                    if (model.YArea != null && model.YArea != 0)
                    {
                        if(model.YYield != null)
                        {
                            model.YAvgMuYield = Decimal.Round(Convert.ToDecimal(model.YYield / model.YArea*1000), 2);
                        }
                       
                    }
                    if (row.GetCell(4) != null)
                    {
                        if (row.GetCell(4).CellType == CellType.Formula)
                        {
                            model.SArea = Decimal.Round(Convert.ToDecimal(row.GetCell(4).NumericCellValue), 4);//大春作面积(万亩）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(4).ToString()))
                            {
                                model.SArea = Convert.ToDecimal(row.GetCell(4).ToString());
                            }
                            else
                            {
                                model.SArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.SArea = 0;
                    }

                    if (row.GetCell(5) != null)
                    {
                        if (row.GetCell(5).CellType == CellType.Formula)
                        {
                            model.SYield = Decimal.Round(Convert.ToDecimal(row.GetCell(5).NumericCellValue), 4);//大春作总产(万吨）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(5).ToString()))
                            {
                                model.SYield = Convert.ToDecimal(row.GetCell(5).ToString());
                            }
                            else
                            {
                                model.SYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.SYield = 0;
                    }

                    if (row.GetCell(6) != null)
                    {
                        if (row.GetCell(6).CellType == CellType.Formula)
                        {
                            model.SOutput = Decimal.Round(Convert.ToDecimal(row.GetCell(6).NumericCellValue),4);//大春作总产值（万元）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(6).ToString()))
                            {
                                model.SOutput = Convert.ToDecimal(row.GetCell(6).ToString());
                            }
                            else
                            {
                                model.SOutput = 0;
                            }
                        }
                    }
                    else
                    {
                        model.SOutput = 0;
                    }


                    if (model.SArea != null && model.SArea != 0)
                    {
                        if (model.SYield != null)
                        {
                            model.SAvgMuYield = Decimal.Round(Convert.ToDecimal(model.SYield / model.SArea*1000), 2);
                        }

                    }

                    if (row.GetCell(7) != null)
                    {
                        if (row.GetCell(7).CellType == CellType.Formula)
                        {
                            model.AArea = Decimal.Round(Convert.ToDecimal(row.GetCell(7).NumericCellValue), 4);//秋作面积(万亩）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(7).ToString()))
                            {
                                model.AArea = Convert.ToDecimal(row.GetCell(7).ToString());
                            }
                            else
                            {
                                model.AArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.AArea = 0;
                    }

                    if (row.GetCell(8) != null)
                    {
                        if (row.GetCell(8).CellType == CellType.Formula)
                        {
                            model.AYield = Decimal.Round(Convert.ToDecimal(row.GetCell(8).NumericCellValue), 4);//秋作总产（万吨）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(8).ToString()))
                            {
                                model.AYield = Convert.ToDecimal(row.GetCell(8).ToString());
                            }
                            else
                            {
                                model.AYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.AYield = 0;
                    }

                    if (row.GetCell(9) != null)
                    {
                        if (row.GetCell(9).CellType == CellType.Formula)
                        {
                            model.AOutput = Decimal.Round(Convert.ToDecimal(row.GetCell(9).NumericCellValue), 4);//秋作总产值（万元）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(9).ToString()))
                            {
                                model.AOutput = Convert.ToDecimal(row.GetCell(9).ToString());
                            }
                            else
                            {
                                model.AOutput = 0;
                            }
                        }
                    }
                    else
                    {
                        model.AOutput = 0;
                    }


                    if (model.AArea != null && model.AArea != 0)
                    {
                        if (model.AYield != null)
                        {
                            model.AAvgMuYield = Decimal.Round(Convert.ToDecimal(model.AYield / model.AArea*1000), 2);
                        }

                    }

                    if (row.GetCell(10) != null)
                    {
                        if (row.GetCell(10).CellType == CellType.Formula)
                        {
                            model.WArea = Decimal.Round(Convert.ToDecimal(row.GetCell(10).NumericCellValue), 4);//冬作面积(万亩）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(10).ToString()))
                            {
                                model.WArea = Convert.ToDecimal(row.GetCell(10).ToString());
                            }
                            else
                            {
                                model.WArea = 0;
                            }
                        }
                    }
                    else
                    {
                        model.WArea = 0;
                    }

                    if (row.GetCell(11) != null)
                    {
                        if (row.GetCell(11).CellType == CellType.Formula)
                        {
                            model.WYield = Decimal.Round(Convert.ToDecimal(row.GetCell(11).NumericCellValue), 4);//冬作总产（万吨）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(11).ToString()))
                            {
                                model.WYield = Convert.ToDecimal(row.GetCell(11).ToString());
                            }
                            else
                            {
                                model.WYield = 0;
                            }
                        }
                    }
                    else
                    {
                        model.WYield = 0;
                    }

                    if (row.GetCell(12) != null)
                    {
                        if (row.GetCell(12).CellType == CellType.Formula)
                        {
                            model.WOutput = Decimal.Round(Convert.ToDecimal(row.GetCell(12).NumericCellValue), 4);//冬作总产值（万元）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(12).ToString()))
                            {
                                model.WOutput = Convert.ToDecimal(row.GetCell(12).ToString());
                            }
                            else
                            {
                                model.WOutput = 0;
                            }
                        }
                    }
                    else
                    {
                        model.WOutput = 0;
                    }

                    if (model.WArea != null && model.WArea != 0)
                    {
                        if (model.WYield != null)
                        {
                            model.WAvgMuYield = Decimal.Round(Convert.ToDecimal(model.WYield / model.WArea*1000), 2);
                        }

                    }
                    model.ReportId = ReportId;
                    list.Add(model);

                }

            }
            return list;
        }
        public ResResult<List<YearProductStatisModel>> getStatisListData(string AreaCode, string FillInYear, string Type,string Pvariety)
        {
            ResResult<List<YearProductStatisModel>> obj = new ResResult<List<YearProductStatisModel>>();
            List<YearProductStatisModel> list = new List<YearProductStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                //var citylist = areaServices.GetPrefectureList();
                //YearProductStatisModel totalModel = new YearProductStatisModel();
                //totalModel.AreaCode = AreaCode;
                //totalModel.AreaName = "合计";
                //totalModel.Pvariety = "合计";
                //totalModel.tag = 2;
                //totalModel.YArea = 0;
                //totalModel.YYield = 0;
                //totalModel.YOutput = 0;
                //totalModel.SArea = 0;
                //totalModel.SYield = 0;
                //totalModel.SOutput = 0;
                //totalModel.AArea = 0;
                //totalModel.AYield = 0;
                //totalModel.AOutput = 0;
                //totalModel.WArea = 0;
                //totalModel.WYield = 0;
                //totalModel.WOutput = 0;
                if (Type.Equals("0"))
                {
                    //foreach (string code in citylist)
                    //{
                    //    List<YearProductStatisModel> templist = new List<YearProductStatisModel>();

                    //    templist = getAreaStatisList(code, FillInYear);

                    //    int len = templist.Count;
                    //    if (len != 0)
                    //    {
                    //        YearProductStatisModel model = templist[len - 1];
                    //        model.AreaName = areaServices.GetByCodeModel(code).model.Name;

                    //        model.tag = 1;
                    //        totalModel.YArea += model.YArea;
                    //        totalModel.YYield += model.YYield;
                    //        totalModel.YOutput += model.YOutput;
                    //        totalModel.SArea += model.SArea;
                    //        totalModel.SYield += model.SYield;
                    //        totalModel.SOutput += model.SOutput;
                    //        totalModel.AArea += model.AArea;
                    //        totalModel.AYield += model.AYield;
                    //        totalModel.AOutput += model.AOutput;
                    //        totalModel.WArea += model.WArea;
                    //        totalModel.WYield += model.WYield;
                    //        totalModel.WOutput += model.WOutput;
                    //        if (totalModel.YArea != 0)
                    //        {
                    //            totalModel.YAvgMuYield = Decimal.Round((decimal)(totalModel.YYield / totalModel.YArea * 1000), 2);
                    //        }
                    //        if (totalModel.SArea != 0)
                    //        {
                    //            totalModel.SAvgMuYield = Decimal.Round((decimal)(totalModel.SYield / totalModel.SArea * 1000), 2);
                    //        }
                    //        if (totalModel.AArea != 0)
                    //        {
                    //            totalModel.AAvgMuYield = Decimal.Round((decimal)(totalModel.AYield / totalModel.AArea * 1000), 2);
                    //        }
                    //        if (totalModel.WArea != 0)
                    //        {
                    //            totalModel.WAvgMuYield = Decimal.Round((decimal)(totalModel.WYield / totalModel.WArea * 1000), 2);
                    //        }
                    //        list.Add(model);
                    //    }
                    // }
                    // list.Add(totalModel);
                    list = getProvinStatisList(FillInYear);
                }
                else
                {
                    list = getProvinVarietyStatisList(FillInYear, Pvariety);
                }
                 
            }
            else if (AreaCode.Substring(4, 8).Equals("00000000"))
            {
                if (Type.Equals("0"))
                {
                    list = getAreaStatisList(AreaCode, FillInYear);
                }
                else
                {
                    list = getVarietyStatisList(AreaCode, FillInYear, Pvariety);
                }
                if(list != null)
                {
                    int len = list.Count;
                    if (len != 0)
                    {
                        list[len - 1].tag = 1;
                    }
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        public List<YearProductStatisModel> getProvinStatisList(string FillInYear)
        {
            List<YearProductStatisModel> list = new List<YearProductStatisModel>();
            string strSql = "select ta.FullName AreaName,tt.* from(";
            strSql += "select case when grouping(t1.ParentCode)=1 then '530000000000' else ParentCode end AreaCode, ";
            strSql += " isnull(sum(t3.YArea),0.00) YArea, ";
            strSql += " isnull(sum(t3.YYield),0.00) YYield,";
            strSql += " isnull(sum(t3.YOutput),0.00) YOutput, ";
            strSql += " isnull(sum(t3.SArea),0.00) SArea, ";
            strSql += " isnull(sum(t3.SYield),0.00) SYield, ";
            strSql += " isnull(sum(t3.SOutput),0.00) SOutput, ";
            strSql += " isnull(sum(t3.AArea),0.00) AArea, ";
            strSql += " isnull(sum(t3.AYield),0.00) AYield, ";
            strSql += " isnull(sum(t3.AOutput),0.00) AOutput, ";
            strSql += " isnull(sum(t3.WArea),0.00) WArea, ";
            strSql += " isnull(sum(t3.WYield),0.00) WYield,";
            strSql += " isnull(sum(t3.WOutput),0.00) WOutput from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotYearProduct t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotYearProduct'  and t2.FillInYear = @FillInYear  ";
            strSql += " and t2.status ='4'  ";
            strSql += " and t1.ParentCode != '530000000000' and t1.Code != '530000000000' ";
            strSql += " group by rollup(t1.ParentCode)";
            strSql += " )tt left join T_Area ta on tt.AreaCode = ta.Code";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<YearProductStatisModel>(strSql, new {FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                    if (list.Count > 0)
                    {
                        for (int i = 0; i < list.Count; i++)
                        {
                            if (list[i].YArea != 0)
                            {
                                list[i].YAvgMuYield = Decimal.Round((decimal)(list[i].YYield / list[i].YArea * 1000), 2);
                            }
                            if (list[i].SArea != 0)
                            {
                                list[i].SAvgMuYield = Decimal.Round((decimal)(list[i].SYield / list[i].SArea * 1000), 2);
                            }
                            if (list[i].AArea != 0)
                            {
                                list[i].AAvgMuYield = Decimal.Round((decimal)(list[i].AYield / list[i].AArea * 1000), 2);
                            }
                            if (list[i].WArea != 0)
                            {
                                list[i].WAvgMuYield = Decimal.Round((decimal)(list[i].WYield / list[i].WArea * 1000), 2);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }
        public List<YearProductStatisModel> getAreaStatisList(string AreaCode, string FillInYear)
        {
            List<YearProductStatisModel> list = new List<YearProductStatisModel>();
            string strSql = "select ta.FullName AreaName,tt.* from(";
            strSql += "select case when grouping(t1.Code)=1 then '" +AreaCode+"' else Code end AreaCode, ";
            strSql += " isnull(sum(t3.YArea),0.00) YArea, ";
            strSql += " isnull(sum(t3.YYield),0.00) YYield,";
            strSql += " isnull(sum(t3.YOutput),0.00) YOutput, ";
            strSql += " isnull(sum(t3.SArea),0.00) SArea, ";
            strSql += " isnull(sum(t3.SYield),0.00) SYield, ";
            strSql += " isnull(sum(t3.SOutput),0.00) SOutput, ";
            strSql += " isnull(sum(t3.AArea),0.00) AArea, ";
            strSql += " isnull(sum(t3.AYield),0.00) AYield, ";
            strSql += " isnull(sum(t3.AOutput),0.00) AOutput, ";
            strSql += " isnull(sum(t3.WArea),0.00) WArea, ";
            strSql += " isnull(sum(t3.WYield),0.00) WYield,";
            strSql += " isnull(sum(t3.WOutput),0.00) WOutput from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotYearProduct t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotYearProduct' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear  ";
            strSql += " and t2.status ='4'  ";
            strSql += " group by rollup(t1.Code)";
            strSql += " )tt left join T_Area ta on tt.AreaCode = ta.Code";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<YearProductStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                    if (list.Count > 0)
                    {
                        for(int i= 0; i < list.Count; i++)
                        {
                            if (list[i].YArea != 0)
                            {
                                list[i].YAvgMuYield = Decimal.Round((decimal)(list[i].YYield / list[i].YArea*1000), 2);
                            }
                            if (list[i].SArea != 0)
                            {
                                list[i].SAvgMuYield = Decimal.Round((decimal)(list[i].SYield / list[i].SArea * 1000), 2);
                            }
                            if (list[i].AArea != 0)
                            {
                                list[i].AAvgMuYield = Decimal.Round((decimal)(list[i].AYield / list[i].AArea * 1000), 2);
                            }
                            if (list[i].WArea != 0)
                            {
                                list[i].WAvgMuYield = Decimal.Round((decimal)(list[i].WYield / list[i].WArea * 1000), 2);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }
        public List<YearProductStatisModel> getVarietyStatisList(string AreaCode, string FillInYear,string Pvariety)
        {
            List<YearProductStatisModel> list = new List<YearProductStatisModel>();
            List<YearProductStatisModel> templist = new List<YearProductStatisModel>();
            List<YearProductStatisModel> qtlist = new List<YearProductStatisModel>();//种类为其他的列表
            // string strSql = "select case when grouping(t3.Pvariety)=1 then '合计' else Pvariety end Pvariety, ";
            // strSql += " case when grouping(t3.Pvariety)=0 and grouping(t1.Code)=1 then '999' else t1.Code end as AreaCode ,";
            string strSql = "select ";
            if (!string.IsNullOrEmpty(Pvariety))
            {
                strSql += " case when grouping(t1.Code)=1 then '"+ AreaCode + "'  else t1.Code end as AreaCode, ";
            }
            else
            {
                strSql += " case when grouping(t3.Pvariety)=1 then '合计' else Pvariety end Pvariety, ";
                strSql += " case when grouping(t3.Pvariety)=0 and grouping(t1.Code)=1 then '999' else t1.Code end as AreaCode ,";
            }
            strSql += " isnull(sum(t3.YArea),0.00) YArea, ";
            strSql += " isnull(sum(t3.YYield),0.00) YYield,";
            strSql += " isnull(sum(t3.YOutput),0.00) YOutput, ";
            strSql += " isnull(sum(t3.SArea),0.00) SArea, ";
            strSql += " isnull(sum(t3.SYield),0.00) SYield, ";
            strSql += " isnull(sum(t3.SOutput),0.00) SOutput, ";
            strSql += " isnull(sum(t3.AArea),0.00) AArea, ";
            strSql += " isnull(sum(t3.AYield),0.00) AYield, ";
            strSql += " isnull(sum(t3.AOutput),0.00) AOutput, ";
            strSql += " isnull(sum(t3.WArea),0.00) WArea, ";
            strSql += " isnull(sum(t3.WYield),0.00) WYield,";
            strSql += " isnull(sum(t3.WOutput),0.00) WOutput from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotYearProduct t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotYearProduct' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear  ";
            
            if (!string.IsNullOrEmpty(Pvariety))
            {
                strSql += " and t3.Pvariety = @Pvariety ";
            }
            else
            {
                strSql += " and t3.Pvariety is not null ";
            }
            strSql += " and t2.status ='4'  ";
            if (!string.IsNullOrEmpty(Pvariety))
            {
                strSql += " group by rollup(t1.Code)";
            }
            else
            {
                strSql += " group by rollup(t3.Pvariety,t1.Code)";
            }
                
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    templist = dbCon.Query<YearProductStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear , Pvariety = Pvariety }).ToList();
                    dbCon.Dispose();//数据库连接释放
                    if (templist.Count > 0)
                    {
                        AreaServices areaService = new AreaServices();
                        bool hasQT = false;//是否有其他品种项
                        //把最后一项的合计和其他项列表与别的分离开来
                        for (int i = 0; i < templist.Count; i++)
                        { 
                            if (templist[i].AreaCode !=null)
                            {
                                if (templist[i].AreaCode.Equals("999"))
                                {
                                    templist[i].AreaName = "小计";
                                }
                                else
                                {
                                    templist[i].AreaName = areaService.GetByCodeModel(templist[i].AreaCode).model.FullName;
                                }
                            }
                            templist[i].YAvgMuYield = 0;
                            templist[i].SAvgMuYield = 0;
                            templist[i].AAvgMuYield = 0;
                            templist[i].WAvgMuYield = 0;
                            if (templist[i].YArea != 0)
                            {
                                templist[i].YAvgMuYield = Decimal.Round((decimal)(templist[i].YYield / templist[i].YArea * 1000), 2);
                            }
                            if (templist[i].SArea != 0)
                            {
                                templist[i].SAvgMuYield = Decimal.Round((decimal)(templist[i].SYield / templist[i].SArea * 1000), 2);
                            }
                            if (templist[i].AArea != 0)
                            {
                                templist[i].AAvgMuYield = Decimal.Round((decimal)(templist[i].AYield / templist[i].AArea * 1000), 2);
                            }
                            if (templist[i].WArea != 0)
                            {
                                templist[i].WAvgMuYield = Decimal.Round((decimal)(templist[i].WYield / templist[i].WArea * 1000), 2);
                            }
                            //if (templist[i].Pvariety.Equals("其他"))
                            //{
                            //    hasQT = true;
                            //    qtlist.Add(templist[i]);
                            //}
                            //else if (!templist[i].Pvariety.Equals("合计"))
                            //{
                            //    list.Add(templist[i]);
                            //}
                            if (string.IsNullOrEmpty(Pvariety))
                            {
                                if (templist[i].Pvariety.Equals("其他"))
                                {
                                    hasQT = true;
                                    qtlist.Add(templist[i]);
                                }
                                else if (!templist[i].Pvariety.Equals("合计"))
                                {
                                    list.Add(templist[i]);
                                }
                            }
                            else
                            {
                                templist[i].Pvariety = Pvariety;
                                list.Add(templist[i]);
                            }
                        }
                        if (string.IsNullOrEmpty(Pvariety))
                        {
                            /*将其他项移至倒数第二项*/
                            if (hasQT)
                            {
                                list.AddRange(qtlist);//添加其他列表
                            }
                            list.Add(templist[templist.Count - 1]);//添加合计项
                        }
                           
                    }
                   
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }
        public List<YearProductStatisModel> getProvinVarietyStatisList(string FillInYear,string Pvariety)
        {
            List<YearProductStatisModel> list = new List<YearProductStatisModel>();
            List<YearProductStatisModel> templist = new List<YearProductStatisModel>();
            List<YearProductStatisModel> qtlist = new List<YearProductStatisModel>();//种类为其他的列表
           
            string strSql = "select ";
            if (!string.IsNullOrEmpty(Pvariety))
            {
                strSql += " case when grouping(t1.ParentCode)=1 then '530000000000'  else t1.ParentCode end as AreaCode, ";
            }
            else
            {
                strSql += " case when grouping(t3.Pvariety)=1 then '合计' else Pvariety end Pvariety, ";
                strSql += " case when grouping(t3.Pvariety)=0 and grouping(t1.ParentCode)=1 then '999' else t1.ParentCode end as AreaCode ,";
            }
            strSql += " isnull(sum(t3.YArea),0.00) YArea, ";
            strSql += " isnull(sum(t3.YYield),0.00) YYield,";
            strSql += " isnull(sum(t3.YOutput),0.00) YOutput, ";
            strSql += " isnull(sum(t3.SArea),0.00) SArea, ";
            strSql += " isnull(sum(t3.SYield),0.00) SYield, ";
            strSql += " isnull(sum(t3.SOutput),0.00) SOutput, ";
            strSql += " isnull(sum(t3.AArea),0.00) AArea, ";
            strSql += " isnull(sum(t3.AYield),0.00) AYield, ";
            strSql += " isnull(sum(t3.AOutput),0.00) AOutput, ";
            strSql += " isnull(sum(t3.WArea),0.00) WArea, ";
            strSql += " isnull(sum(t3.WYield),0.00) WYield,";
            strSql += " isnull(sum(t3.WOutput),0.00) WOutput from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotYearProduct t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotYearProduct'  and t2.FillInYear = @FillInYear  ";
            strSql += " and t1.ParentCode != '530000000000'  and t1.Code != '530000000000' ";
           
            if (!string.IsNullOrEmpty(Pvariety))
            {
                 strSql += " and t3.Pvariety = @Pvariety ";
            }
            else
            {
                strSql += " and t3.Pvariety is not null ";
            }
            strSql += " and t2.status ='4'  ";
            if (!string.IsNullOrEmpty(Pvariety))
            {
                strSql += " group by rollup(t1.ParentCode)";
            }
            else
            {
                strSql += " group by rollup(t3.Pvariety,t1.ParentCode)";
            }
            
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    templist = dbCon.Query<YearProductStatisModel>(strSql, new { FillInYear = FillInYear, Pvariety= Pvariety }).ToList();
                    dbCon.Dispose();//数据库连接释放
                    if (templist.Count > 0)
                    {
                        AreaServices areaService = new AreaServices();
                        bool hasQT = false;//是否有其他品种项
                        for (int i = 0; i < templist.Count; i++)
                        {
                            if(templist[i].AreaCode != null )
                            {
                                if (templist[i].AreaCode.Equals("999"))
                                {
                                    templist[i].AreaName = "小计";
                                }
                                else
                                {
                                    templist[i].AreaName = areaService.GetByCodeModel(templist[i].AreaCode).model.FullName;
                                }
                                
                            }
                            templist[i].YAvgMuYield = 0;
                            templist[i].SAvgMuYield = 0;
                            templist[i].AAvgMuYield = 0;
                            templist[i].WAvgMuYield = 0;

                            if (templist[i].YArea != 0)
                            {
                                templist[i].YAvgMuYield = Decimal.Round((decimal)(templist[i].YYield / templist[i].YArea * 1000), 2);
                            }
                            if (templist[i].SArea != 0)
                            {
                                templist[i].SAvgMuYield = Decimal.Round((decimal)(templist[i].SYield / templist[i].SArea * 1000), 2);
                            }
                            if (templist[i].AArea != 0)
                            {
                                templist[i].AAvgMuYield = Decimal.Round((decimal)(templist[i].AYield / templist[i].AArea * 1000), 2);
                            }
                            if (templist[i].WArea != 0)
                            {
                                templist[i].WAvgMuYield = Decimal.Round((decimal)(templist[i].WYield / templist[i].WArea * 1000), 2);
                            }
                            if (string.IsNullOrEmpty(Pvariety))
                            {
                                if (templist[i].Pvariety.Equals("其他"))
                                {
                                    hasQT = true;
                                    qtlist.Add(templist[i]);
                                }
                                else if (!templist[i].Pvariety.Equals("合计"))
                                {
                                    list.Add(templist[i]);
                                }
                            }
                            else
                            {
                                templist[i].Pvariety = Pvariety;
                                list.Add(templist[i]);
                            }
                           
                        }
                        if (string.IsNullOrEmpty(Pvariety))
                        {
                            /*将其他项移至倒数第二项*/
                            if (hasQT)
                            {
                                list.AddRange(qtlist);//添加其他列表
                            }
                            list.Add(templist[templist.Count - 1]);//添加合计项
                        }
                            
                    }
                    
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }
        
    }
}